# RelationLoader is to load the relations between the tables containing all the attributes we may be targeting. 
# By loading relations, we are able to build the connections specifically in SQL where clause.
# 
# Here is the example of what SQL script is going to be like:
#
# select p.patientid, p.mrn, p.dob, r.accessionnumber, r.datetime_request 
# from patient_table p, request_table r, resultdetails_table rd, diagnosis_codes_table d 
# where p.patientid=r.patientid and r.requestid=rd.requestid and d.diagnosiscode=rd.diagnosiscode 
# and p.sex="M" and p.dob>"1968-01-01 00:00:00" and p.dob<"1988-01-01 00:00:00" 
# and rd.diagnosiscode="M87600";
#
# The relation information can be retrieved from the database schema. Take MySQL for instance, the database information_schema 
# has stored all sorts of metadata information. Particularly, the table KeyColumnUsage has stored the foreign key and primary key
# constraints.
#

from DatabaseConnector import connection

class RelationLoader:
    
    def __new__(cls, *args, **kargs):
        if cls.instance is None:
            cls.instance = object.__new__(cls, *args, **kargs)
        return cls.instance
    
    def __init__(self):
        self.db = "hosrep"
# Relation array consists of [[table name], [column name], [referenced table name], [referenced column name]]
        self.relation_array = [[],[],[],[]]
        self.i = 0
        self.shortest_num = 0
        self.shortest_num_thresh = 0
# Shortest_path_idx is formated as [["End point1","End point2"]], functioning as the metadata of the known path between 2 tables
        self.shortest_path_idx = []
# Shortest_path is recording the known path between 2 tables
        self.InitRelationTable()
        
    def InitRelationTable(self):
        cursor = connection.cursor()
        cursor.execute("SELECT table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.KEY_COLUMN_USAGE K WHERE table_name in (\"request_table\",\"resultdetails_table\", \"patient_table\") and referenced_table_name in (\"request_table\",\"resultdetails_table\", \"patient_table\") and constraint_name <> \"PRIMARY\" and constraint_schema = '"+self.db+"'" )
        for tuple in cursor.fetchall():
            for j in range(0, len(tuple)):
                self.relation_array[j].append(tuple[j])
#        for i in range(0, len(self.relation_array[0])):
#            print self.relation_array[0][i], self.relation_array[1][i], self.relation_array[2][i], self.relation_array[3][i]
    
    def FindRecursion(self, table1, table2, processed, path):
# If table1 is connected with other table and if it is connected directly with table2
        adjacent = self.GetAdjacentTable(table1, processed)
        self.i = self.i + 1
#        print "Round: " + str(self.i)
#        print table1 
#        print table2
#        print "adjacent " + str(adjacent)
#        print "processed " + str(processed)
#        print str(adjacent.count(table2))
#        print str(path) + "\n"
        if adjacent <> []:
            if adjacent.count(table2) == 0:
                processed.append(table1)
# Else we have to search from its adjacent nodes to see if it is connected indirectly via other nodes
                for i in range(0, len(adjacent)):
                    if self.FindRecursion(adjacent[i], table2, processed, path) == True:
                        path.append(adjacent[i])
                        return True
            if adjacent.count(table2) <> 0:
                path.append(table2)
                return True        
        else:
            return False
        
    def FindRelatonPath(self, table1, table2):
        path = []
        processed = []
        if table1 == "": 
            path.append(table2)
        elif table2 == "": 
            path.append(table1)
        else:
            self.FindRecursion(table1, table2, processed, path)
            path.append(table1)
        return path
 
    def GetAdjacentTable(self, table, processed):
        adjacent = []
        if self.relation_array[0].count(table) <> 0:
            for i in range(0, len(self.relation_array[0])):
                if self.relation_array[0][i] == table:
                    if adjacent.count(self.relation_array[2][i]) == 0 and processed.count(self.relation_array[2][i]) == 0:
                        adjacent.append(self.relation_array[2][i])
        if self.relation_array[2].count(table) <> 0:
            for i in range(0, len(self.relation_array[2])):
                if self.relation_array[2][i] == table:
                    if adjacent.count(self.relation_array[0][i]) == 0 and processed.count(self.relation_array[0][i]) == 0:
                        adjacent.append(self.relation_array[0][i])
        return adjacent
            
if __name__ == "__main__":
    loader = RelationLoader()
#    path = []
#    print loader.FindRelatonPath("patient_table", "")
    
#    if loader.GetAdjacentTable("patient_table",[]) <> []:
#        print  loader.GetAdjacentTable("patient_table",[])         

    
    